Re: [SQL] cast text as date

At 20:58 +0300 on 16/6/98, Richard Lynch wrote:


>
> ERROR:  function date(text) does not exist
>
> I don't know what version because my ISP installed it, and there seems to
> be no file I can read that tells me, and postmaster -v isn't defined.
>
> There are two postgresql directories.  One is labeled 6.2.1
> The other is just pgsql.

Oh, I see. Well, you shouldn't use 'date'. It's a limited datatype. I
always use 'datetime' - it has much more functionality.

Now here is an example for you:

testing=> \d example3

Table    = example3
+--------------------------+------------------------+-------+
|           Field          |          Type          | Length|
+--------------------------+------------------------+-------+
| mon_year                 | text                   |   var |
+--------------------------+------------------------+-------+

testing=> SELECT * FROM example3;
mon_year
--------
05/98
06/99
12/98
01/99
(4 rows)

testing=> SET DATESTYLE TO 'european';
SET VARIABLE

testing=> SELECT datetime( '01/'::text || mon_year )
testing-> FROM example3;
datetime
----------------------------
Fri 01 May 00:00:00 1998 IDT
Tue 01 Jun 00:00:00 1999 IDT
Tue 01 Dec 00:00:00 1998 IST
Fri 01 Jan 00:00:00 1999 IST
(4 rows)

I think this is what you wanted: You append the strings '01/' with the
month-and-year field, convert to datetime, and that is comparable as a
date. For example, here you select the row which has already expired:

testing=> SELECT *
testing-> FROM example3
testing-> WHERE 'now' > datetime( '01/'::text || mon_year );
mon_year
--------
05/98
(1 row)

(Of course the result would have been more interesting if there were a few
more fields...).

You can define the above conversion as an SQL function for your convenience:

testing=> CREATE FUNCTION monyear2datetime( text ) RETURNS datetime
testing-> AS 'SELECT datetime( ''01/''::text || $1 )'
testing-> LANGUAGE 'sql';
CREATE

testing=> SELECT mon_year, monyear2datetime( mon_year )
testing-> FROM example3;
mon_year|monyear2datetime
--------+----------------------------
05/98   |Fri 01 May 00:00:00 1998 IDT
06/99   |Tue 01 Jun 00:00:00 1999 IDT
12/98   |Tue 01 Dec 00:00:00 1998 IST
01/99   |Fri 01 Jan 00:00:00 1999 IST
(4 rows)

Herouth
Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma